package users;

import java.sql.Connection;
import java.sql.Date;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Vector;

public class Customer {
	
	private Connection con;
	
	public Customer(Connection con) {
		this.con = con;
	}
	
	// create a new customer
	public boolean newCustomer(String cID, String pword, String name, String address, String phone) throws SQLException {
		
		int success = 0;
		
		// for debugging purposes
		System.out.println("INSERT into Customer " +
				"(cid, password, name, address, phone) VALUES " +
				"('" + cID + "', '" +  pword + "', '" + name + "', '" + address +
				"', '" + phone + "')");
		
		// sql statement
		Statement stmt1 = con.createStatement();
		success = stmt1.executeUpdate("INSERT into Customer " +
				"(cid, password, name, address, phone) VALUES " +
				"('" + cID + "', '" +  pword + "', '" + name + "', '" + address +
				"', '" + phone + "')");
		
		// close sql statement and return result
		stmt1.close();
		
		
		if(success >= 1) {
			return true;
		} else {
			return false;
		}
	}
	
	// search for specific items
	public ResultSet searchItem(String singer, String category, String title) throws SQLException{
		Statement stmt = con.createStatement();
		ResultSet rs;
		
		String itemTitle = "I.title = '" + title + "'";
		String itemSinger = "L.name = '" + singer + "'";
		String itemCategory = "I.category = '" + category + "'";
		String finalQuery = null;
		String query = "SELECT I.* FROM Item I, LeadSinger L, Stored S";
		
		if (singer.isEmpty() && category.isEmpty() && title.isEmpty()){
			finalQuery = query;
		} else if(singer.isEmpty() && category.isEmpty()) {
			finalQuery = query + " WHERE " + itemTitle;
		} else if (category.isEmpty() && title.isEmpty()) {
			finalQuery = query + " WHERE " + itemSinger;
		} else if (singer.isEmpty() && title.isEmpty()) {
			finalQuery = query + " WHERE " + itemCategory;
		} else if (singer.isEmpty()) {
			finalQuery = query + " WHERE " + itemCategory + " AND " + itemTitle;
		} else if (category.isEmpty()) {
			finalQuery = query + " WHERE " + itemTitle + " AND " + itemSinger;
		} else if (title.isEmpty()) {
			finalQuery = query + " WHERE " + itemSinger + " AND " + itemCategory;
		} else {
			finalQuery = query + " WHERE " + itemSinger + " AND " + itemCategory + " AND " + itemTitle;
		}
		
		System.out.println(finalQuery);
		rs = stmt.executeQuery(finalQuery);
		
		return rs;
	}
	
	// find if a particular customer exists
	public boolean findCustomer(String cID, String pword) throws SQLException {
		
		int success = 0;
		
		// for debugging purposes
		System.out.println("Select cid, password FROM Customer " +
				"WHERE cid = '" + cID + "' and password = '" + pword + "'");
		
		// sql statement
		Statement stmt1 = con.createStatement();
		success = stmt1.executeUpdate("Select cid, password FROM Customer " +
				"WHERE cid = '" + cID + "' and password = '" + pword + "'");
		
		// close sql statement and return result
		stmt1.close();
		
		
		if(success >= 1) {
			return true;
		} else {
			return false;
		}
	}
	
	// get all items that can be purchased
	public ResultSet allItems() throws SQLException {
		
		Statement stmt = con.createStatement();
		ResultSet result = stmt.executeQuery("SELECT * FROM Item");
		
		return result;	
	}
	
	// get the warehouse stock of an item
	public int getItemQuantity(String upc) throws SQLException {
		Statement stmt = con.createStatement();
		ResultSet result = stmt.executeQuery("SELECT stock FROM Stored WHERE upc = '" + upc +
				"' AND name = 'Warehouse'");
		
		// return warehouse stock of a particular item
		if(result.next()) {
			return result.getInt(1);
			
		} else{
			return 0;
		}
	}

	// get the stats of a customer from the customer table
	public ResultSet retrieveUserStats(String cID, String pword) throws SQLException {
			
			// for debugging purposes
			System.out.println("Select * FROM Customer " +
					"WHERE cid = '" + cID + "' and password = '" + pword + "'");
			
			// sql statement
			Statement stmt1 = con.createStatement();
			ResultSet result = stmt1.executeQuery("Select * FROM Customer " +
					"WHERE cid = '" + cID + "' and password = '" + pword + "'");
			
			return result;
	}

	// get the receipt ID needed for a purchase
	public int getRID() {
		int rid = 0;
		
		// for debugging purposes
		System.out.println("Select MAX(receiptID) FROM Purchase");
		
		// sql statement
		ResultSet result = null;
		Statement stmt1;
		try {
			stmt1 = con.createStatement();
			result = stmt1.executeQuery("Select MAX(receiptID) FROM Purchase");
			
			if(result.next()) {
				rid = result.getInt(1);
			}
			
			stmt1.close();
		} catch (SQLException e) {
			System.out.println("Could not get maximum receipt id");
		}
		
		System.out.println("new rid number: " + (rid + 1));
		return rid + 1;
	}

	// get the estimated date of delivery
	public Date findEstimatedDate() {
		Date eDate = null;
		int unfilledOrders = 0;
		int totalOrders = 0, deliveredOrders = 0;
		
		// for debugging purposes
		System.out.println("Select COUNT(deliveredDate) AS totalPurchases FROM Purchase");
		System.out.println("Select COUNT(deliveredDate) AS unfilledPurchases FROM Purchase");
		
		// sql statement to get the number of undelivered orders
		Statement stmt1, stmt2;
		ResultSet res1, res2;
		try {
			stmt1 = con.createStatement();
			stmt2 = con.createStatement();
			res1 = stmt1.executeQuery("Select COUNT(receiptID) AS totalPurchases FROM Purchase");
			res2 = stmt2.executeQuery("Select COUNT(deliveredDate) AS unfilledPurchases FROM Purchase");
			
			if(res1.next() && res2.next()) {
				totalOrders = res1.getInt(1);
				deliveredOrders = res2.getInt(1);
			}
				
			unfilledOrders = totalOrders - deliveredOrders;
			
			// for debugging purposes
			System.out.println("Number of days before delivery: " + unfilledOrders);
			
			stmt1.close();
			stmt2.close();
		} catch (SQLException e) {
			System.out.println("Could not get amount of undelivered orders");
		}
		
		eDate = new Date(System.currentTimeMillis() + (unfilledOrders/2)*24*60*60*1000);
		
		System.out.println("shipdate: " + eDate.toString());
		return eDate;
	}
	
	// add to purchase table
	public boolean addToPurchase(int rID, Date pDate, String cid, String name, int cNo, String expire, Date eDate) {
		int success = 0;
		
		// sql statement
		Statement stmt1;
		try {
			stmt1 = con.createStatement();
			
			// for debugging
			
			System.out.println("INSERT INTO Purchase " + 
					"VALUES (" +rID+ ", +TO_DATE('" +pDate+ "', 'YYYY-MM-DD'), '" +cid+ "', '" +name+
					"', " +cNo+ ", +TO_DATE('" +expire+ "', 'YYYY-MM-DD'), +TO_DATE('" +eDate+ "', 'YYYY-MM-DD'), NULL)");
			
			success = stmt1.executeUpdate("INSERT INTO Purchase " + 
					"VALUES (" +rID+ ", +TO_DATE('" +pDate+ "', 'YYYY-MM-DD'), '" +cid+ "', '" +name+
					"', " +cNo+ ", +TO_DATE('" +expire+ "', 'YYYY-MM-DD'), +TO_DATE('" +eDate+ "', 'YYYY-MM-DD'), NULL)");
			
			stmt1.close();
		} catch (SQLException e) {
			System.out.println("Could not add to Purchase table.");
			e.printStackTrace();
		}
		
		if(success == 0) {
			return false;
		} else {
			return true;
		}
	}

	// add to purchase item table
	public boolean addToPurchaseItem(int rid, Object upc, Object qty) {
		int success = 0;
		
		// sql statement
		Statement stmt1;
		try {
			stmt1 = con.createStatement();
			
			// for debugging
			System.out.println("INSERT INTO PurchaseItem (receiptID, upc, quantity) " + 
					"VALUES (" +rid+ ", '" +upc+ "', " +qty+ ")");
			
			success = stmt1.executeUpdate("INSERT INTO PurchaseItem (receiptID, upc, quantity) " + 
					"VALUES (" +rid+ ", '" +upc+ "', " +qty+ ")");
			
			stmt1.close();
		} catch (SQLException e) {
			System.out.println("Could not add to purchaseItem table.");
		}
		
		if(success == 0) {
			return false;
		} else {
			return true;
		}
	}
	
	// delete customer from the customer table
	public void cancelAccount(String cid, String pword) {
		// sql statement
		Statement stmt1, stmt2, stmt3, stmt4;
		ResultSet results;
		int receiptID = 0;
		try {
			stmt1 = con.createStatement();
			stmt2 = con.createStatement();
			stmt3 = con.createStatement();
			stmt4 = con.createStatement();
			
			// get receipt ids
			results = stmt2.executeQuery("SELECT receiptID FROM Purchase WHERE cid = '" +cid+ "'");
			
			// delete purchased items
			while(results.next()){
				receiptID = results.getInt(1);
				stmt3.executeUpdate("DELETE PurchaseItem WHERE receiptID = " +receiptID);
			}
			
			// delete purchases
			stmt4.executeUpdate("DELETE Purchase WHERE cid = '" +cid+ "'");
			
			// for debugging
			System.out.println("DELETE Customer " + 
					"WHERE cid = '" +cid+ "' AND password = '" +pword+ "'");
			
			// delete customer
			stmt1.executeUpdate("DELETE Customer " + 
					"WHERE cid = '" +cid+ "' AND password = '" +pword+ "'");
			
			stmt1.close();
			stmt2.close();
			stmt3.close();
			stmt4.close();
		} catch (SQLException e) {
			System.out.println("Could not delete customer from customer table.");
		}
	}

	// get the most popular items
	public ResultSet getPopularItems(String date, int num) {
		ResultSet items = null;
		
		// if the number is <= 0, don't do anything
		if (num < 1) {
			return items;
		}
		
		
		String query = "SELECT title, company, tStock, tQuantity " +
			"from Item NATURAL INNER JOIN " + 
			"(SELECT upc, sum(stock) AS tStock FROM Stored GROUP BY upc) NATURAL INNER JOIN " +
			"(SELECT p1.upc, sum(p1.quantity) AS tQuantity FROM PurchaseItem p1, Purchase p " +
			"WHERE p1.receiptID = p.receiptID AND p.purchaseDate = +TO_DATE('" +date+ "', 'YYYY-MM-DD') GROUP BY p1.upc) " + 
			"ORDER BY tQuantity DESC";	
		System.out.println(query);
		
		try {
			Statement stmt = con.createStatement();
			items = stmt.executeQuery(query);
		} catch (SQLException e) {
			System.out.println("Could not get the most popular items.");
			e.printStackTrace();
		}
		
		return items;
	}
}
